﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using OnlineCooperation.Entity;

namespace OnlineCooperation.DB
{
    public class UserDB
    {
        String strConn;
        datasetMgn DSMgn;

        //构造函数， 为strConn赋值
        public UserDB()
        {
            strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DSMgn = new datasetMgn();
        }

        /// <summary>
        /// 邀请未注册用户时向数据库中插入一条用户记录
        /// 插入一条仅有邮箱的用户记录，id仍为自增字段
        /// </summary>
        /// <param name="email">所邀请的未注册用户的邮箱</param>
        /// <param name="teamid">发出邀请的团队的编号</param>
        /// <returns>插入的结果：返回1成功插入;返回-1表示已存在同邮箱的注册用户,不必插入;返回0表示已存在同邮箱的未注册用户,不必插入</returns>
        /// 已存在同邮箱的未注册用户表明同时有多个团队邀请该未注册用户加入,或同一团队已经邀请过该未注册用户 
        /// 插入一条仅有邮箱的用户记录并且相应添加rel完整记录
        public int Insert(String email, int teamid)
        {
            String CurrentUserID;
            //邮箱判重
            String strSql = "SELECT * FROM `userinfo` WHERE `Email`=\'" + email + "\'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                //不存在同邮箱的用户记录，可以进行插入
                strSql = "INSERT INTO `userinfo`(`UserName`,`Email`,`Password`,`Icon`) VALUES(?UserName0, ?Email0, ?Password0, ?Icon0)";
                MySqlParameter[] parameters = new MySqlParameter[4];
                parameters[0] = new MySqlParameter("?UserName0", MySqlDbType.VarChar);
                parameters[0].Value = null;
                parameters[1] = new MySqlParameter("?Email0", MySqlDbType.VarChar);
                parameters[1].Value = email;
                parameters[2] = new MySqlParameter("?Password0", MySqlDbType.VarChar);
                parameters[2].Value = null;
                parameters[3] = new MySqlParameter("?Icon0", MySqlDbType.VarChar);
                parameters[3].Value = null;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);
                strSql = "SELECT * FROM `userinfo` WHERE `Email`=\'" + email + "\'";
                CurrentUserID = DSMgn.Readstr(strSql, 0, strConn);
                RelUserTeamDB RUTDB = new RelUserTeamDB();
                RUTDB.addRel(teamid, Convert.ToInt32(CurrentUserID));
                return 1;                                                                                   //插入成功
            }
            CurrentUserID = DSMgn.Readstr(strSql, 0, strConn);
            String PreviousName = DSMgn.Readstr(strSql, 1, strConn);
            if (PreviousName != "")
            {
                //已存在同邮箱的注册用户
                //reluserteam中添加相应记录
                RelUserTeamDB RUTDB = new RelUserTeamDB();
                RUTDB.addRel(teamid, Convert.ToInt32(CurrentUserID));
                //post中添加相应记录
                //在团队内发布新鲜事，表明该用户加入了该团队
                strSql = "SELECT max(`PostID`) FROM `post`";
                String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
                if (CurrentMaxPostID == "")
                    CurrentMaxPostID = "-1";
                strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
                MySqlParameter[] parameters2 = new MySqlParameter[6];
                parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
                parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
                parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
                parameters2[1].Value = 0;                                                            //所有新鲜事的StateID为0
                parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
                parameters2[2].Value = "用户" + PreviousName + "加入了本团队，欢迎^_^";
                parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
                parameters2[3].Value = 0;
                parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
                parameters2[4].Value = Convert.ToInt32(teamid);
                parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
                parameters2[5].Value = DateTime.Now;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

                return -1;                                                                                   //已存在同邮箱的注册用户，不必插入
            }
            return 0;                                                                                       //已存在同邮箱的未注册用户，不必插入
        }

        /// <summary>
        /// 向数据库中插入一条用户记录，完成用户注册
        /// </summary>
        /// <param name="user">用户，其ID字段初始为无效字段</param>
        /// <returns>注册的状态：返回1成功，0表示存在同邮箱用户，-1表示存在同名用户，插入失败</returns>
        /// 这里可能出现只存在邮箱，而没有其他字段的用户，这种用户是其他成员邀请的未注册用户，
        /// 这种情况把该记录补足，完成插入
        public int Insert(User user)
        {
            //用户名判重
            String strSql = "SELECT * FROM `userinfo` WHERE `UserName`=\'" + user.Name + "\'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count != 0)
                return -1;                                                                                   //存在同名用户，注册失败

            //邮箱判重
            strSql = "SELECT * FROM `userinfo` WHERE `Email`=\'" + user.Email + "\'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                //不存在同邮箱用户，可以进行注册
                strSql = "INSERT INTO `userinfo`(`UserName`,`Email`,`Password`,`Icon`) VALUES(?UserName0, ?Email0, ?Password0, ?Icon0)";
                MySqlParameter[] parameters = new MySqlParameter[4];
                parameters[0] = new MySqlParameter("?UserName0", MySqlDbType.VarChar);
                parameters[0].Value = user.Name;
                parameters[1] = new MySqlParameter("?Email0", MySqlDbType.VarChar);
                parameters[1].Value = user.Email;
                parameters[2] = new MySqlParameter("?Password0", MySqlDbType.VarChar);
                parameters[2].Value = user.Pwd;
                parameters[3] = new MySqlParameter("?Icon0", MySqlDbType.VarChar);
                parameters[3].Value = user.Icon;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);
                return 1;                                                                                   //注册成功
            }
            String PreviousName = DSMgn.Readstr(strSql, 1, strConn);
            if (PreviousName == "")
            {
                //已被邀请，取得其UserID以及所在team编号TeamID，继续注册
                strSql = "UPDATE `userinfo` SET `UserName` = ?UserName1, `Password` = ?Password1, `Icon` = ?Icon1 WHERE `Email`=\'" + user.Email + "\'";
                MySqlParameter[] parameters = new MySqlParameter[3];
                parameters[0] = new MySqlParameter("?UserName1", MySqlDbType.VarChar);
                parameters[0].Value = user.Name;
                parameters[1] = new MySqlParameter("?Password1", MySqlDbType.VarChar);
                parameters[1].Value = user.Pwd;
                parameters[2] = new MySqlParameter("?Icon1", MySqlDbType.VarChar);
                parameters[2].Value = user.Icon;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

                //在邀请该用户的所有团队内发布新鲜事，表明该用户加入了团队
                RelUserTeamDB RUTDB = new RelUserTeamDB();
                List<Team> AllTeam = RUTDB.GetAllTeam(user);

                foreach (Team item in AllTeam)
                {
                    strSql = "SELECT max(`PostID`) FROM `post`";
                    String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
                    if (CurrentMaxPostID == "")
                        CurrentMaxPostID = "-1";
                    strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
                    MySqlParameter[] parameters2 = new MySqlParameter[6];
                    parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
                    parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
                    parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
                    parameters2[1].Value = 0;                                                            //所有新鲜事的StateID为0
                    parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
                    parameters2[2].Value = "用户" + user.Name + "加入了本团队，欢迎^_^";
                    parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
                    parameters2[3].Value = 0;
                    parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
                    parameters2[4].Value = item.ID;
                    parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
                    parameters2[5].Value = DateTime.Now;
                    MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);
                }

                return 1;                                                                                    //注册成功
            }
            return 0;                                                                                       //存在同邮箱的已注册用户，注册失败
        }

        /// <summary>
        /// 在数据库中根据用户名查询到特定用户
        /// </summary>
        /// <param name="name">用户名</param>
        /// <returns>查询结果：若查询到，则返回该用户；否则返回ID为-1的用户</returns>
        public User GetUserByName(String name)
        {
            String strSql = "SELECT `UserID`, `Email`, `Password`, `Icon` FROM `userinfo` WHERE `UserName`=\'" + name + "\'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                User user = new User();
                user.ID = -1;
                return user;
            }                                                                                                    //不存在该用户名的用户，查询失败
            else
            {
                User user = new User(Convert.ToInt32(DSMgn.Readstr(strSql, 0, strConn)), name, DSMgn.Readstr(strSql, 1, strConn), DSMgn.Readstr(strSql, 2, strConn), DSMgn.Readstr(strSql, 3, strConn));
                return user;
            }                                                                                                    //查询成功
        }

        /// <summary>
        /// 在数据库中根据邮箱查询到特定用户
        /// </summary>
        /// <param name="email">邮箱</param>
        /// <returns>查询结果：若查询到，则返回该用户；否则返回ID为-1的用户</returns>
        public User GetUserByEmail(String email)
        {
            String strSql = "SELECT `UserID`, `UserName`, `Password`, `Icon` FROM `userinfo` WHERE `Email`=\'" + email + "\'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                User user = new User();
                user.ID = -1;
                return user;
            }                                                                                                    //不存在该用户名的用户，查询失败
            else
            {
                User user = new User(Convert.ToInt32(DSMgn.Readstr(strSql, 0, strConn)), DSMgn.Readstr(strSql, 1, strConn), email, DSMgn.Readstr(strSql, 2, strConn), DSMgn.Readstr(strSql, 3, strConn));
                return user;
            }                                                                                                    //查询成功
        }

        /// <summary>
        /// 在数据库中根据用户编号查询到特定用户
        /// </summary>
        /// <param name="ID">用户编号</param>
        /// <returns>查询结果：若查询到，则返回该用户；否则返回ID为-1的用户</returns>
        public User GetUserByID(int ID)
        {
            String strSql = "SELECT `UserName`, `Email`, `Password`, `Icon` FROM `userinfo` WHERE `UserID`='" + ID + "'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                User user = new User();
                user.ID = -1;
                return user;
            }                                                                                                    //不存在该用户名的用户，查询失败
            else
            {
                User user = new User(ID, DSMgn.Readstr(strSql, 0, strConn), DSMgn.Readstr(strSql, 1, strConn), DSMgn.Readstr(strSql, 2, strConn), DSMgn.Readstr(strSql, 3, strConn));
                return user;
            }                                                                                                    //查询成功
        }

        /// <summary>
        /// 在数据库中修改用户个人信息
        /// </summary>
        /// <param name="user">用户，除了ID和Email以外，信息已更新</param>
        /// <returns>修改结果：若存在同名用户，则修改失败返回0；否则修改成功返回1</returns>
        public int Update(User user)
        {
            //获取原来的用户名
            String strSql = "SELECT `UserName` FROM `userinfo` WHERE `UserID`='" + user.ID + "'";
            DataRow dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
            String PreviousName = dr[0].ToString();

            //用户名判重
            strSql = "SELECT `UserID` FROM `userinfo` WHERE `UserName`=\'" + user.Name + "\'";
            dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
            String PreviousID;
            if (dr != null)
            {
                PreviousID = dr[0].ToString();
                if (Convert.ToInt32(PreviousID) != user.ID)
                    return 0;                                                                               //存在同名不同ID用户，修改失败
            }

            strSql = "UPDATE `userinfo` SET `UserName` = ?UserName0, `Password` = ?Password0, `Icon` = ?Icon0 WHERE `UserID`='" + user.ID + "'";
            MySqlParameter[] parameters = new MySqlParameter[3];
            parameters[0] = new MySqlParameter("?UserName0", MySqlDbType.VarChar);
            parameters[0].Value = user.Name;
            parameters[1] = new MySqlParameter("?Password0", MySqlDbType.VarChar);
            parameters[1].Value = user.Pwd;
            parameters[2] = new MySqlParameter("?Icon0", MySqlDbType.VarChar);
            parameters[2].Value = user.Icon;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

            if (PreviousName == user.Name)
                return 1;                                                                                       //修改成功，用户名未修改

            //若该用户修改了用户名，则在该用户所属所有团队内发布新鲜事，表明该用户修改了用户名
            RelUserTeamDB RUTDB = new RelUserTeamDB();
            List<Team> AllTeam = RUTDB.GetAllTeam(user);

            foreach (Team item in AllTeam)
            {
                strSql = "SELECT max(`PostID`) FROM `post`";
                dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
                String CurrentMaxPostID;
                if (dr == null)
                    CurrentMaxPostID = "-1";
                else
                    CurrentMaxPostID = dr[0].ToString();

                strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
                MySqlParameter[] parameters2 = new MySqlParameter[6];
                parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
                parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
                parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
                parameters2[1].Value = 0;                                                          //所有新鲜事的StateID为0
                parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
                parameters2[2].Value = "邮箱为" + user.Email + "的用户已更名为" + user.Name + "哦~";
                parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
                parameters2[3].Value = 0;
                parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
                parameters2[4].Value = item.ID;
                parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
                parameters2[5].Value = DateTime.Now;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);
            }

            return 1;                                                                                       //修改成功
        }

        /// <summary>
        /// 在数据库中删除用户
        /// </summary>
        /// <param name="user">用户</param>
        /// <returns>删除结果：删除相应的用户信息，团队关联，个人文件，返回1成功，0失败</returns>
        public int Delete(User user)
        {
            String strSql = "DELETE  FROM `userinfo` WHERE `UserID`='" + user.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除reluserteam中的相应记录
            strSql = "DELETE  FROM `reluserteam` WHERE `UserID`='" + user.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除userfile中的相应记录
            strSql = "DELETE  FROM `userfile` WHERE `UserID`='" + user.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);

            //在该用户所属所有团队内发布新鲜事，表明该用户已注销
            RelUserTeamDB RUTDB = new RelUserTeamDB();
            List<Team> AllTeam = RUTDB.GetAllTeam(user);

            foreach (Team item in AllTeam)
            {
                strSql = "SELECT max(`PostID`) FROM `post`";
                DataRow dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
                String CurrentMaxPostID;
                if (dr == null)
                    CurrentMaxPostID = "-1";
                else
                    CurrentMaxPostID = dr[0].ToString();

                strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2)";
                MySqlParameter[] parameters2 = new MySqlParameter[6];
                parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
                parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
                parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
                parameters2[1].Value = 0;                                                       //所有新鲜事的StateID为0
                parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
                parameters2[2].Value = "用户" + user.Name + "已注销！";
                parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
                parameters2[3].Value = 0;
                parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
                parameters2[4].Value = item.ID;
                parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
                parameters2[5].Value = DateTime.Now;
                MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);
            }

            return 1;                                                                                       //删除成功
        }
    }
}